using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using EBE.COM.Domain;
using EBE.COM.Interfaces;

namespace EBE.COM.Data
{
  public class CategoryRepository : RepositoryBase, IRepository<ICategory>
  {
    public CategoryRepository(string connectionString)
      : base(connectionString)
    {
    }

    public IQueryable<ICategory> FindAll()
    {
      var con = new SqlConnection(ConnectionString);
      var list = new List<ICategory>();
      using (con)
      {
        var sql = string.Format("SELECT * FROM [Category]");
        var cmd = new SqlCommand(sql, con);
        con.Open();
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          var entity = new Category();
          GetEntity(reader, entity);
          list.Add(entity);
        }
      }
      return list.OrderBy(e => e.Name).AsQueryable();
    }

    public IQueryable<ICategory> Find(Expression<Func<ICategory, bool>> predicate)
    {
      throw new NotImplementedException();
    }

    public ICategory FindById(int id)
    {
      var con = new SqlConnection(ConnectionString);
      var entity = new Category();
      using (con)
      {
        var sql = string.Format("SELECT * FROM [Category] WHERE Id = {0}", id);
        var cmd = new SqlCommand(sql, con);
        con.Open();
        var reader = cmd.ExecuteReader();
        while (reader.Read())
        {
          GetEntity(reader, entity);
        }
      }
      return entity;
    }

    private static void GetEntity(IDataRecord reader, ICategory entity)
    {
      entity.Id = (int)reader["Id"];
      entity.Name = (string)reader["Name"];
    }

    public void Add(ICategory newEntity)
    {
      var con = new SqlConnection(ConnectionString);
      using (con)
      {
        var sql = string.Format("INSERT INTO [Category] (Name) VALUES ('{0}')", newEntity.Name);
        var cmd = new SqlCommand(sql, con);
        con.Open();
        var reader = cmd.ExecuteReader();
      }
    }

    public void Remove(ICategory entity)
    {
      var con = new SqlConnection(ConnectionString);
      using (con)
      {
        var sql = string.Format("DELETE FROM [Category] WHERE id = {0}", entity.Id);
        var cmd = new SqlCommand(sql, con);
        con.Open();
        var reader = cmd.ExecuteReader();
      }
    }

    public void Update(ICategory entity)
    {
      var con = new SqlConnection(ConnectionString);
      using (con)
      {
        var sql = string.Format("UPDATE [Category] SET Name = '{0}' WHERE id = {1}", entity.Name, entity.Id);
        var cmd = new SqlCommand(sql, con);
        con.Open();
        var reader = cmd.ExecuteReader();
      }
    }
  }
}
